package com.infore.statisticsAndAlarm.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.WeekData;

@Repository
public interface IWeekDataRespository extends JpaRepository<WeekData, String>,JpaSpecificationExecutor<WeekData>{

//	/**分析单因子，根据因子站点时间段筛选**/
//	List<WeekData> findByStationCodeInAndFactorCodeInAndBeginTimeIsAfterAndEndTimeIsBeforeOrderByBeginTimeAsc(List<String> stationCode,
//			List<String> factorCode, Date start, Date end);
	
	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于单点自动站报表
	@Query(value = "select t.station_code as MN,t.factor_code as CODE,t.begin_time as TIME,t.data_value_avg as VALUE"
			+ " from WEEK_DATA t  where (t.station_code in (:mnList)) " + 
			"and (t.factor_code in (:projectCodes)) and t.begin_time >= to_date(:weekSatrt, 'yyyy-mm-dd') " + 
			"and t.end_time <= to_date(:weekEnd, 'yyyy-mm-dd') order by t.begin_time asc", nativeQuery = true)
	List<Map<String, Object>> audioSingReport(@Param("weekSatrt") String weekSatrt, @Param("weekEnd") String weekEnd,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);
	
	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于多站自动站报表
	@Query(value = "select t.station_code as MN, t.factor_code as CODE , to_char( avg(t.data_value_avg),'fm999999990.999999999') as VALUE ,to_char(sum(t.real_num)) as COUNT "
			+ "from WEEK_DATA t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) "
			+ "and t.begin_time >= to_date(:weekSatrt, 'yyyy-mm-dd') and t.end_time <= to_date(:weekEnd, 'yyyy-mm-dd') group by t.station_code,t.factor_code order by t.station_code", nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("weekSatrt") String weekSatrt, @Param("weekEnd") String weekEnd,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);
}
